﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TweetJSON.JSON.Entity;
using System.Data;

namespace TweetJSON.DAL
{
    class TweetStatDB
    {
        public void InsertUser(TwitterUser user)
        {
            string command = @"INSERT INTO twitter_user  (user_id, user_name, screen_name, description, profile_image_url, location)
                                SELECT * FROM 
                                    (SELECT
                                    " + user.id.ToString() + " as id,'" + user.name.Replace("'", "").Replace("\\", "") + "' as user_name ,'" + user.screen_name.Replace("'", "").Replace("\\", "") + "' as screen_name,'" + (string.IsNullOrEmpty(user.description) ? "" : user.description.Replace("'", "").Replace("\\", ""))
                                      + "' as description,'" + user.profile_image_url + "' as profile_image_url,'" + (string.IsNullOrEmpty(user.location) ? "" : user.location.Replace("'", "").Replace("\\", "")) + "' as location"
                                      + ") AS tmp  WHERE NOT EXISTS ( SELECT 1 FROM twitter_user WHERE user_id = " + user.id.ToString()+"); ";
            MySQLClient db = new MySQLClient();
            db.InsertData(command);
        }
        public void InsertTweet(Tweet tweet)
        {
            string command = @"INSERT INTO tweet(tweet_id, user_id, tweet_text, created_at, retweet_count, latitude, longitude)
                                VALUES (" + tweet.id.ToString() + ", " + tweet.user.id + ", '" + tweet.text.Replace("'", "").Replace("\\", "") + "', '" + tweet.created_at + "', " +
                                         tweet.RetweetCount + ", " + tweet.latitude.ToString() + ", " + tweet.longitude.ToString() + ");";

            MySQLClient db = new MySQLClient();

            db.InsertData(command);

            foreach (TweetTopic topic in tweet.entities.hashtags)
            {
                command = @"SELECT topic_id FROM topic WHERE topic_text='" + topic.text + "';";
                DataTable dt = db.getData(command);
                int topicId = 0;
                if (dt == null || dt.Rows.Count == 0)
                {

                    command = @"INSERT INTO topic(topic_text) VALUES ('" + topic.text + "'); "
                                + " SELECT LAST_INSERT_ID();";
                    dt = db.getData(command);
                    DataRow row = dt.Rows[0];
                    topicId = Int32.Parse(row[0].ToString());
                }
                else
                {
                    DataRow row = dt.Rows[0];
                    topicId = Int32.Parse(row[0].ToString());
                }
                command = @"INSERT INTO tweet_topic(tweet_id, topic_id) VALUES ( " + tweet.id + ", " + topicId + ");";
                db.InsertData(command);
            }


        }

    }
}
